﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using IBLL;
using IDAL;
using Microsoft.EntityFrameworkCore;
using Models.Enums;
using Models;
using Models.DTO;
using Models.Enums;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace BLL
{
    public class PurchaseBLL : IPurchaseBLL
    {
        private IPurchaseDAL _purchaseDAL;
        private IPurchaseDetailDAL _purchaseDetailDAL;
        private ISupplierInfoDAL _supplierInfoDAL;
        private ICategoryDAL _categoryDAL;
        private IProductInfoDAL _productInfoDAL;
        private DemoManageSysDbContext _dbContext;

        public PurchaseBLL(
            IPurchaseDAL purchaseDAL,
            IPurchaseDetailDAL purchaseDetailDAL,
            ICategoryDAL categoryDAL,
            ISupplierInfoDAL supplierInfoDAL,
            IProductInfoDAL productInfoDAL,
            DemoManageSysDbContext dbContext
            )
        {
            _purchaseDAL = purchaseDAL;
            _purchaseDetailDAL = purchaseDetailDAL;
            _supplierInfoDAL = supplierInfoDAL;
            _categoryDAL = categoryDAL;
            _productInfoDAL = productInfoDAL;
            _dbContext = dbContext;
        }

        #region 获取进货信息表的方法 (GetPurchasees)
        /// <summary>
        /// 获取进货信息表的方法
        /// </summary>
        /// <param name="page"></param>
        /// <param name="limit"></param>
        /// <param name="purchaseName"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public List<GetPurchaseDTO> GetPurchase(int page, int limit, string purchaseName, out int count)
        {
            var data = from p in _purchaseDAL.GetEntities().Where(p => p.IsDelete == false)
                       join s in _supplierInfoDAL.GetEntities().Where(p => p.IsDelete == false)
                       on p.SupplierId equals s.Id
                       into PStemp
                       from ps in PStemp.DefaultIfEmpty()
                       join pi in _productInfoDAL.GetEntities().Where(p => p.IsDelete == false)
                       on p.ProductId equals pi.Id
                       into PPItemp
                       from ppi in PPItemp.DefaultIfEmpty()
                       select new GetPurchaseDTO
                       {
                           Id = p.Id,
                           SupplierId = ps.SupplierName,
                           PurchaseName = p.PurchaseName,
                           ProductId = ppi.ProductName,
                           Num = p.Num,
                           PurchaseAmount = p.PurchaseAmount,
                           Description = p.Description,
                           CreateTime = p.CreateTime
                       };

            if (!string.IsNullOrWhiteSpace(purchaseName))
            {
                //条件精准查询
                data = data.Where(p => p.PurchaseName == purchaseName);
            }
            //计算数据总数
            count = data.Count();
            //分页(降序)
            var listpage = data.OrderByDescending(u => u.CreateTime).Skip(limit * (page - 1)).Take(limit).ToList();

            return listpage;
        }
        #endregion

        #region 创建进货信息 (CreatePurchase)
        /// <summary>
        /// 创建进货信息
        /// </summary>
        /// <param name="cate">用户实体</param>
        /// <param name="msg">返回消息</param>
        /// <returns></returns>
        public bool CreatePurchase(Purchase purc, out string msg)
        {
            if (string.IsNullOrWhiteSpace(purc.PurchaseName))
            {
                msg = "名称不能为空";
                return false;
            }
            //验证部门是否存在
            if (_purchaseDAL.GetEntities().FirstOrDefault(it => it.PurchaseName == purc.PurchaseName) != null)
            {
                msg = "名称已经存在";
                return false;
            }
            purc.Id = Guid.NewGuid().ToString();//用户id
            purc.CreateTime = DateTime.Now;//设置时间
            bool IsSuccess = _purchaseDAL.CreateEntity(purc);//调用方法
            msg = IsSuccess ? $"添加{purc.PurchaseName}成功" : "添加用户失败";

            return IsSuccess;
        }
        #endregion

        #region 删除的方法 (DeletePurchase)
        /// <summary>
        /// 删除的方法
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeletePurchase(string id)
        {
            Purchase purc = _purchaseDAL.GetEntityById(id);
            if (purc == null)
            {
                return false;
            }
            _purchaseDAL.DeleteEntity(purc);
            return true;
        }
        #endregion

        #region 批量删除的方法 (DeleteListPurchase)
        /// <summary>
        /// 批量删除的方法
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteListPurchase(List<string> ids)
        {
            List<Purchase> consList = _purchaseDAL.GetEntities().Where(it => ids.Contains(it.Id)).ToList();
            foreach (var item in ids)
            {
                Purchase purc = _purchaseDAL.GetEntityById(item);
                if (consList == null)
                {
                    return false;
                }
                purc.IsDelete = true;
                purc.DeleteTime = DateTime.Now;

                _purchaseDAL.UpdateEntity(purc);
            }
            return true;
        }
        #endregion

        #region 修改的方法 (UpdatePurchase)
        /// <summary>
        /// 修改的方法
        /// </summary>
        /// <param name="dept"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool UpdatePurchase(Purchase purc, out string msg)
        {
            if (string.IsNullOrWhiteSpace(purc.PurchaseName))
            {
                msg = $"名称不能为空";
                return false;
            }
            Purchase entity = _purchaseDAL.GetEntityById(purc.Id);
            if (entity.Id == null)
            {
                msg = "ID不存在";
                return false;
            }
            //判断重复
            if (entity.PurchaseName != purc.PurchaseName)
            {
                var data = _purchaseDAL.GetEntities().FirstOrDefault(it => it.PurchaseName == purc.PurchaseName);
                if (data != null)
                {
                    msg = "部门名已经被占用";
                    return false;
                }
            }

            entity.PurchaseName = purc.PurchaseName;
            entity.SupplierId = purc.SupplierId;
            entity.PurchaseAmount = purc.PurchaseAmount;
            entity.Num = purc.Num;
            entity.Description = purc.Description;

            bool IsSuccess = _purchaseDAL.UpdateEntity(entity);//调用方法

            msg = IsSuccess ? $"修改成功" : "修改失败";

            return IsSuccess;
        }
        #endregion

        #region 根据ID获取数据返回赋值 (GetMenuInfoById)
        /// <summary>
        /// 根据ID获取数据返回赋值
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Purchase GetPurchaseById(string id)
        {
            return _purchaseDAL.GetEntityById(id);
        }
        #endregion

        #region 获取数据库列表赋值下拉框 (GetSelectOption)
        /// <summary>
        /// 获取数据库列表赋值下拉框
        /// </summary>
        /// <returns></returns>
        public object GetSelectOption()
        {
            var supplierInfoData = _supplierInfoDAL.GetEntities().Where(it => it.IsDelete == false).Select(p => new
            {
                value = p.Id,
                title = p.SupplierName,
            }).ToList();

            var productInfoData = _productInfoDAL.GetEntities().Where(it => it.IsDelete == false).Select(p => new
            {
                value = p.Id,
                title = p.ProductName,
            }).ToList();

            return new
            {
                supplierInfoData = supplierInfoData,
                productInfoData = productInfoData
            };
        }
        #endregion

        #region Excel进货入库方法 (UpLoad)
        /// <summary>
        /// Excel进货入库方法
        /// </summary>
        /// <param name="stream">文件流</param>
        /// <param name="extension">文件后缀</param>
        /// <param name="userId">用户ID</param>
        /// <param name="msg">返回消息</param>
        /// <returns></returns>
        public bool UpLoad(Stream stream, string extension, string ProductName, out string msg)
        {
            IWorkbook wk = null;
            if (extension.Equals(".xls"))
            {
                wk = new HSSFWorkbook(stream);
            }
            else
            {
                wk = new XSSFWorkbook(stream);
            }

            stream.Close();
            stream.Dispose();

            //操作Excel表
            ISheet sheet = wk.GetSheetAt(0);//根据索引获取Excel表
            int rowNum = sheet.LastRowNum;//获取表有多少行数据

            //开启事务
            using (var transation = _dbContext.Database.BeginTransaction())
            {
                try
                {
                    for (int i = 1; i <= rowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);//获取表中每行的数据

                        ICell cell = row.GetCell(0);//获取每行的第一列数据
                        string value = cell.ToString();

                        ICell cellNum = row.GetCell(2);
                        string StrNum = cellNum.ToString();

                        ICell cellPrice = row.GetCell(3);
                        string StrPrice = cellPrice.ToString();

                        int IntNum;
                        int IntPrice;

                        bool par = int.TryParse(StrNum, out IntNum);//比较判断Num（数量）是否为Int类型
                        bool par2 = int.TryParse(StrPrice, out IntPrice);//比较判断Num（数量）是否为Int类型
                        if (par == false)
                        {
                            transation.Rollback();//回滚事务
                            msg = $"第{i + 1}行进货的实际购买数量有误";
                            return false;
                        }

                        Purchase purchase = _purchaseDAL.GetEntities().FirstOrDefault(it =>it.IsDelete == false && it.PurchaseName == value);
                        if (purchase == null)
                        {
                            transation.Rollback();//回滚事务
                            msg = $"第{i + 1}行进货不存在或已被删除,{value}出现错误";
                            return false;
                        }

                        ProductInfo product = _productInfoDAL.GetEntities().FirstOrDefault(it => it.IsDelete == false && it.ProductName == value);
                        if (product == null)
                        {
                            transation.Rollback();//回滚事务
                            msg = $"第{i + 1}行进货不存在或已被删除,{value}出现错误";
                            return false;
                        }

                        PurchaseDetail purchaseDetail = new PurchaseDetail()
                        {
                            Id = Guid.NewGuid().ToString(),
                            PurchaseId = purchase.Id,
                            CreateTime = DateTime.Now,
                            ProductId = product.Id,
                            ProductNum = IntNum,
                            ProductPrice = IntPrice,
                            Type = (int)PurchaseDetailTypeEnums.入库

                        };
                        _dbContext.PurchaseDetail.Add(purchaseDetail);
                        bool IsOk = _dbContext.SaveChanges() > 0;
                        if (IsOk == false)
                        {
                            transation.Rollback();//回滚事务
                            msg = $"第{i + 1}行进货记录失败,{value}出现错误";
                            return false;
                        }

                        product.ProductNum += IntNum;
                        purchase.PurchaseAmount += IntPrice;
                        purchase.Num += IntNum;

                        _dbContext.Entry(purchaseDetail).State = EntityState.Modified;
                        IsOk = _dbContext.SaveChanges() > 0;
                        if (IsOk == false)
                        {
                            transation.Rollback();//回滚事务
                            msg = $"第{i + 1}行进货记录失败,{value}出现错误";
                            return false;
                        }

                    }

                    transation.Commit();
                    msg = "入库成功";
                    return true;
                }
                catch (Exception ex)
                {
                    transation.Rollback();
                    msg = ex.Message;
                    return false;
                }
            }
        }
        #endregion

        #region 出入库记录的接口 (GetDownLoad)
        /// <summary>
        /// 出入库记录的接口
        /// </summary>
        /// <param name="downLoadName"></param>
        /// <returns></returns>
        public Stream GetDownLoad(out string downLoadName)
        {
            var datas = (from pd in _purchaseDetailDAL.GetEntities()
                         join p in _purchaseDAL.GetEntities().Where(p => p.IsDelete == false)
                         on pd.PurchaseId equals p.Id
                         into PPDtemp
                         from ppd in PPDtemp.DefaultIfEmpty()
                         join pi in _productInfoDAL.GetEntities().Where(p => p.IsDelete == false)
                         on pd.ProductId equals pi.Id
                         into PDPItemp
                         from pdpi in PDPItemp.DefaultIfEmpty()
                         select new
                         {
                             ppd.PurchaseName,
                             pdpi.ProductName,
                             Type = pd.Type == (int)PurchaseDetailTypeEnums.入库 ? "入库" : "出库",
                             pd.ProductNum,
                             pd.CreateTime,
                             pd.ProductPrice
                         }).ToList();

            //获取当前项目运行的目录
            string path = Directory.GetCurrentDirectory();
            string fileName = "出入库记录导出" + DateTime.Now.ToString("yyyy-MM-dd hh mm ss") + ".xlsx";
            //拼接路径
            string filePath = Path.Combine(path, fileName);

            //Excel
            IWorkbook wk = null;
            string extension = Path.GetExtension(filePath);

            using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite))
            {
                if (extension.Equals(".xls"))
                {
                    wk = new HSSFWorkbook();
                }
                else
                {
                    wk = new XSSFWorkbook();
                }

                ISheet sheet = wk.CreateSheet("你想要的名字");

                IRow row = sheet.CreateRow(0);

                #region 创建表头
                //ICell cell = row.CreateCell(0);
                //cell.SetCellValue("进货名称");

                string[] titleList =
                {
                    "进货人",
                    "商品名称",
                    "出入库类型",
                    "商品数量",
                    "出入库时间",
                    "进货总金额",
                };

                //循环添加表头
                for (int i = 0; i < titleList.Length; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(titleList[i]);
                }
                #endregion

                #region 创建表主体
                for (int i = 0; i < datas.Count; i++)
                {
                    var data = datas[i];

                    IRow tempRow = sheet.CreateRow(i + 1);//从第二行开始创建正文

                    ICell tempCell1 = tempRow.CreateCell(0);//每行第一列
                    tempCell1.SetCellValue(data.PurchaseName);

                    ICell tempCell2 = tempRow.CreateCell(1);//每行第二列
                    tempCell2.SetCellValue(data.ProductName);

                    ICell tempCell3 = tempRow.CreateCell(2);//每行第三列
                    tempCell3.SetCellValue(data.Type);

                    ICell tempCell4 = tempRow.CreateCell(3);//每行第四列
                    tempCell4.SetCellValue(data.ProductNum);

                    ICell tempCell5 = tempRow.CreateCell(4);//每行第五列
                    tempCell5.SetCellValue(data.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"));

                    ICell tempCell6 = tempRow.CreateCell(5);//每行第六列
                    tempCell6.SetCellValue(data.ProductPrice);

                }


                #endregion

                //把Excel写入文件流
                wk.Write(fs);

                FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);

                downLoadName = fileName;//返回Excel文件名

                return fileStream;
            }

        }
        #endregion
    }
}
